import pymysql
import time
from selenium import webdriver
import webbrowser
from selenium.webdriver.common.by import By
from unittest import TestCase
import xlrd
from ddt import ddt, data, unpack

wb = xlrd.open_workbook(filename="li.xlsx")
st = wb.sheet_by_index(8)
rows = int(st.nrows)
date = []
da = []

con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance", charset='utf8')
cursor = con.cursor()
sql = '''select count(id) from loan where id=(select id from user where username='lisi')'''
sql2 = '''select balance from bankcard where id=(select id from user where username='lisi')'''
cursor.execute(sql)
cursor.execute(sql2)
num = cursor.execute(sql)
money = int(cursor.execute(sql2))
for row in range(1, rows):
    date = st.row_values(row)
    username = str(date[0])
    password = str(int(date[1]))
    amount = str(int(date[2]))
    term = str(int(date[3]))
    da.append([username, password, amount, term])




    @ddt
    class Testjiedai(TestCase):
        @data(*da)
        @unpack
        def testjiedai1(self, username, password, amount, term):
            driver = webdriver.Chrome()
            driver.get("http://localhost:90")
            driver.maximize_window()  # 最大化页面
            time.sleep(1)
            driver.find_element(By.XPATH, "//input[@name='username']").send_keys(username)
            time.sleep(1)
            driver.find_element(By.XPATH, "//input[@id='password']").send_keys(password)
            time.sleep(1)
            driver.find_element(By.XPATH, "//*[@id='login_btn']").click()
            time.sleep(1)
            driver.find_element(By.XPATH, "//*[@id='leftbaraside']/div[2]/nav/ul/li[3]/a").click()
            time.sleep(1)
            driver.find_element(By.XPATH, "//*[@id='leftbaraside']/div[2]/nav/ul/li[3]/ul/li[2]/a").click()
            time.sleep(1)
            driver.find_element(By.XPATH, "//*[@id='amount']").send_keys(amount)
            driver.find_element(By.XPATH, "//*[@id='term']").send_keys(term)
            time.sleep(1)
            try:
                driver.find_element(By.XPATH, "//*[@id='submit']").click()
                time.sleep(1)
                driver.find_element(By.XPATH,
                                    "/html/body/div[2]/div[2]/div/div/div/div/div/div/div/div[4]/button[1]").click()
                time.sleep(1)
                if int(amount) < 100000000 and int(amount) >= 100:
                    if int(term) < 10000:
                        self.assertEqual(cursor.execute(sql), (num + 1))
                        time.sleep(1)
                        driver.quit()
                        driver1 = webdriver.Chrome()
                        driver1.get("http://localhost:90")
                        driver1.maximize_window()
                        time.sleep(1)
                        driver1.find_element(By.XPATH, "//input[@name='username']").send_keys('admin')
                        time.sleep(1)
                        driver1.find_element(By.XPATH, "//input[@id='password']").send_keys('123456')
                        time.sleep(1)
                        driver1.find_element(By.XPATH, "//*[@id='login_btn']").click()
                        time.sleep(1)
                        driver1.find_element(By.XPATH, "//*[@id='leftbaraside']/div[2]/nav/ul/li[5]/a").click()
                        time.sleep(1)
                        driver1.find_element(By.XPATH, "//*[@id='leftbaraside']/div[2]/nav/ul/li[5]/ul/li[1]/a").click()
                        time.sleep(1)
                        driver1.find_element(By.XPATH,
                                             "/html/body/div/div/main/div/div/div/div/div[2]/div/table/tbody/tr[1]/td[7]/button[1]").click()
                        time.sleep(1)
                        driver1.find_element(By.XPATH,
                                             "/html/body/div[2]/div[2]/div/div/div/div/div/div/div/div[4]/button[1]").click()
                        time.sleep(1)
                        driver1.quit()
                        driver2 = webdriver.Chrome()
                        driver2.get("http://localhost:90")
                        driver2.find_element(By.XPATH, "//input[@name='username']").send_keys(username)
                        time.sleep(1)
                        driver2.find_element(By.XPATH, "//input[@id='password']").send_keys(password)
                        time.sleep(1)
                        driver2.find_element(By.XPATH, "//*[@id='login_btn']").click()
                        time.sleep(1)
                        driver2.find_element(By.XPATH, "//*[@id='leftbaraside']/div[2]/nav/ul/li[4]/a").click()
                        time.sleep(1)
                        driver2.find_element(By.XPATH, "//*[@id='leftbaraside']/div[2]/nav/ul/li[4]/ul/li[3]/a").click()
                        time.sleep(1)
                        self.assertEqual(cursor.execute(sql2), int(money) + int(amount))
                    else:
                        self.assertEqual(cursor.execute(sql), (num))
                else:
                    (cursor.execute(sql), (num))
            except:
                self.assertEqual(cursor.execute(sql), (num))
            driver.quit()


